﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 文章表操作类(初步审核通过)
    /// </summary>
    public class PostDAO : IPost
    {
        /// <summary>
        /// 向文章表中插入一行数据, 返回插入数据的主键ID, 插入失败返回-1
        /// </summary>
        /// <param name="post">要插入的文章对象</param>
        /// <returns>返回插入数据的主键ID, 插入失败返回-1</returns>
        public int Insert(Post post)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Posts]
                                    (
                                        [post_slug],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_excerpt],
                                        [post_template],
                                        [post_auther_id],
                                        [post_view_count],
                                        [post_comment_count],
                                        [post_comment_status],
                                        [post_status],
                                        [post_type],
                                        [post_istop],
                                        [post_ping_status],
                                        [post_pinged],
                                        [post_password],
                                        [post_parent_id]
                                    )
                                    OUTPUT inserted.[post_id]
                                    VALUES 
                                    ( 
                                        @Slug,
                                        @Title,
                                        @SubTitle,
                                        @PubDate,
                                        @Update,
                                        @Content,
                                        @Excerpt,
                                        @Template,
                                        @AutherId,
                                        @ViewCount,
                                        @CommentCount,
                                        @CommentStatus,
                                        @Status,
                                        @Type,
                                        @IsTop,
                                        @PingStatus,
                                        @Pinged,
                                        @Password,
                                        @ParentId
                                    );";
            try
            {
                return (int)SqlHelper.ExecuteScalar(sql,
                       new SqlParameter("@Slug", post.Slug),
                       new SqlParameter("@Title", string.IsNullOrEmpty(post.Title) ? System.DBNull.Value : (object)post.Title),
                       new SqlParameter("@SubTitle", string.IsNullOrEmpty(post.SubTitle) ? System.DBNull.Value : (object)post.SubTitle),
                       new SqlParameter("@PubDate", post.PubDate),
                       new SqlParameter("@Update", post.Update),
                       new SqlParameter("@Content", post.Content),
                       new SqlParameter("@Excerpt", post.Excerpt),
                       new SqlParameter("@Template", post.Template),
                       new SqlParameter("@AutherId", post.AutherId),
                       new SqlParameter("@ViewCount", post.ViewCount),
                       new SqlParameter("@CommentCount", post.CommentCount),
                       new SqlParameter("@CommentStatus", post.CommentStatus),
                       new SqlParameter("@Status", post.Status),
                       new SqlParameter("@Type", post.Type),
                       new SqlParameter("@IsTop", post.IsTop),
                       new SqlParameter("@PingStatus", post.PingStatus),
                       new SqlParameter("@Pinged", string.IsNullOrEmpty(post.Pinged) ? System.DBNull.Value : (object)post.Pinged),
                       new SqlParameter("@Password", string.IsNullOrEmpty(post.Password) ? System.DBNull.Value : (object)post.Password),
                       new SqlParameter("@ParentId", post.ParentId));
            }
            catch
            {
                return -1;
            }
        }

        /// <summary>
        /// 根据文章ID删除对应文章表中的文章记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除的文章ID</param>
        /// <param name="deleteComment">是否删除该文章对应所有评论</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id, bool deleteComment)
        {
            string sql = string.Empty;
            if (deleteComment)
            {
                //连同评论删除
                //注意这种T-SQL写法,也是可行的,但是不能少分号哦!
                sql = @"DELETE FROM [&#WednTablePrefix%&_Posts] WHERE [post_id]=@Id;
                              DELETE FROM [&#WednTablePrefix%&_Relations] WHERE [object_id]=@Id AND [object_type]=0;
                              DELETE FROM [&#WednTablePrefix%&_Comments] WHERE [comment_post_id]=@Id;";
            }
            else
            {
                //只删除文章
                sql = @"DELETE FROM [&#WednTablePrefix%&_Posts] WHERE [post_id]=@Id;
                              DELETE FROM [&#WednTablePrefix%&_Relations] WHERE [object_id]=@Id AND [object_type]=0;";
            }
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据文章ID数组删除对应文章表中的文章记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除的文章ID数组</param>
        /// <param name="deleteComment">是否删除该文章对应所有评论</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids, bool deleteComment)
        {
            //拼接要删除的ID
            string ps = string.Join(",", ids);
            string sql = string.Empty;
            //此处写法不安全, 可以用参数化代替, 为了省事,,,,
            if (deleteComment)
            {
                //连同评论删除
                sql = @"DELETE FROM [&#WednTablePrefix%&_Posts] WHERE [post_id] in ({0});
                            DELETE FROM [&#WednTablePrefix%&_Relations] WHERE [object_id] in ({0}) AND [object_type]=0;
                            DELETE FROM [&#WednTablePrefix%&_Comments] WHERE [comment_post_id] in ({0});";
            }
            else
            {
                //只删除文章
                sql = @"DELETE FROM [&#WednTablePrefix%&_Posts] WHERE [post_id] in ({0});
                           DELETE FROM [&#WednTablePrefix%&_Relations] WHERE [object_id] in ({0}) AND [object_type]=0;";
            }
            return SqlHelper.ExecuteNonQuery(string.Format(sql, ps));
        }

        /// <summary>
        /// 更新一个完整文章对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="post">更新的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdatePost(Post post)
        {
            string sql = @"UPDATE [&#WednTablePrefix%&_Posts]
                                   SET
                                        [post_slug]=@Slug,
                                        [post_title]=@Title,
                                        [post_subtitle]=@SubTitle,
                                        [post_pubdate]=@PubDate,
                                        [post_update]=@Update,
                                        [post_content]=@Content,
                                        [post_excerpt]=@Excerpt,
                                        [post_template]=@Template,
                                        [post_auther_id]=@AutherId,
                                        [post_view_count]=@ViewCount,
                                        [post_comment_count]=@CommentCount,
                                        [post_comment_status]=@CommentStatus,
                                        [post_status]=@Status,
                                        [post_type]=@Type,
                                        [post_istop]=@IsTop,
                                        [post_ping_status]=@PingStatus,
                                        [post_pinged]=@Pinged,
                                        [post_password]=@Password,
                                        [post_parent_id]=@ParentId
                                    WHERE 
                                        [post_id]=@Id;";
            return ExecuteSql(post, sql);
        }

        /// <summary>
        /// 根据文章ID数组批量更新文章状态, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要更新的文章ID数组</param>
        /// <param name="status">文章当前状态(0:公开, 1:加密, 2:私密, 3:继承, 4:草稿, 5:自动草稿, 6:回收站...)(忽略该条件则传入-1)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateStatus(int[] ids, int status)
        {
            //拼接要更新的ID
            string ps = string.Join(",", ids);
            string sql = @"UPDATE [&#WednTablePrefix%&_Posts]
                                   SET
                                        [post_status]=@Status
                                    WHERE 
                                        [post_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Status", status));
        }

        /// <summary>
        /// 根据文章ID更新文章标题,副标题,别名,内容, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要更新的文章ID</param>
        /// <param name="title">更新的标题(不更新则传入null)</param>
        /// <param name="subTitle">更新的副标题(不更新则传入null)</param>
        /// <param name="slug">更新的别名(不更新则传入null)</param>
        /// <param name="content">更新的内容(不更新则传入null)</param>
        /// <param name="excerpt">更新的摘要(不更新则传入null)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateInfo(int id, string title, string subTitle, string slug, string content, string excerpt)
        {
            List<string> strs = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            bool flag = false;
            //动态SQL, 唉, 据说也不安全纳...
            if (!string.IsNullOrEmpty(title))
            {
                flag = true;
                strs.Add("[post_title]=@Title");
                ps.Add(new SqlParameter("@Title", title));
            }
            if (!string.IsNullOrEmpty(subTitle))
            {
                flag = true;
                strs.Add("[post_subtitle]=@SubTitle");
                ps.Add(new SqlParameter("@SubTitle", subTitle));
            }
            if (!string.IsNullOrEmpty(slug))
            {
                flag = true;
                strs.Add("[post_slug]=@Slug");
                ps.Add(new SqlParameter("@Slug", slug));
            }
            if (!string.IsNullOrEmpty(content))
            {
                flag = true;
                strs.Add("[post_content]=@Content");
                ps.Add(new SqlParameter("@Content", content));
            }
            if (!string.IsNullOrEmpty(excerpt))
            {
                flag = true;
                strs.Add("[post_excerpt]=@Excerpt");
                ps.Add(new SqlParameter("@Excerpt", excerpt));
            }
            if (flag)
            {
                //SQL语句有效
                string sql = @"UPDATE [&#WednTablePrefix%&_Posts]
                                        SET
                                            {0}
                                        WHERE 
                                            [post_id]=@Id;";
                sql = string.Format(sql, string.Join(",", strs));
                ps.Add(new SqlParameter("@Id", id));
                return SqlHelper.ExecuteNonQuery(sql, ps.ToArray());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 根据文章ID更新文章浏览次数, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要更新的文章ID</param>
        /// <param name="addCount">增加的数量(一般为1)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateViewCount(int id, int addCount)
        {
            string sql = @"UPDATE
                                        [&#WednTablePrefix%&_Posts]
                                    SET
                                        [post_view_count]=[post_view_count]+@AddCount
                                    WHERE
                                        [post_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@AddCount", addCount),
                new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据文章ID更新文章浏览次数, 返回执行结果受影响行数
        /// 可以使用触发器实现更完美, 此处未实现(原因为了拓展), 待续..
        /// </summary>
        /// <param name="id">要更新的文章ID</param>
        /// <param name="addCount">增加的数量(一般为1)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateCommentCount(int id, int addCount)
        {
            string sql = @"UPDATE [&#WednTablePrefix%&_Posts]
                                    SET
                                        [post_comment_count]=[post_comment_count]+@AddCount
                                    WHERE
                                        [post_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@AddCount", addCount),
                new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("post_id", "&#WednTablePrefix%&_Posts");
        }

        /// <summary>
        /// 检查表中是否已经存在该别名, 返回一个布尔值, 存在则为True; 不存在则为False
        /// </summary>
        /// <param name="slug">要确定的别名</param>
        /// <returns>存在返回True; 不存在返回False</returns>
        public bool ExistsSlug(string slug)
        {
            return SqlHelper.ColumnExistsValue("&#WednTablePrefix%&_Posts", "post_slug", slug);
        }

        /// <summary>
        /// 选取文章信息表中文章总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null或空值)</param>
        /// <returns>总数</returns>
        public int SelectPostsCount(string where)
        {
            string sql = "SELECT COUNT (1) FROM [&#WednTablePrefix%&_Posts] {0};";
            where = !string.IsNullOrEmpty(where) ? ("WHERE " + where) : string.Empty;
            sql = string.Format(sql, where);
            return (int)SqlHelper.ExecuteScalar(sql);
        }

        /// <summary>
        /// 选取文章信息表中文章总数
        /// </summary>
        /// <param name="where">选择条件</param>
        /// <param name="parmeters">参数列表</param>
        /// <returns>总数</returns>
        private int SelectPostsCount(string where, SqlParameter[] parmeters)
        {
            string sql = "SELECT COUNT (1) FROM [&#WednTablePrefix%&_Posts] {0};";
            where = !string.IsNullOrEmpty(where) ? ("WHERE " + where) : string.Empty;
            sql = string.Format(sql, where);
            return (int)SqlHelper.ExecuteScalar(sql, parmeters);
        }

        /// <summary>
        /// 选取文章信息表中所有文章信息, 返回文章信息对象集合, 如果不存在则返回null
        /// </summary>
        /// <returns>所有文章信息集合</returns>
        public List<Post> SelectPostList()
        {
            string sql = @"SELECT
                                        [post_id],
                                        [post_slug],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_excerpt],
                                        [post_template],
                                        [post_auther_id],
                                        [post_view_count],
                                        [post_comment_count],
                                        [post_comment_status],
                                        [post_status],
                                        [post_type],
                                        [post_istop],
                                        [post_ping_status],
                                        [post_pinged],
                                        [post_password],
                                        [post_parent_id]
                                   FROM
                                        [&#WednTablePrefix%&_Posts];";
            return Select(sql);
        }

        /// <summary>
        /// 根据指定状态, 指定浏览等级, 指定页码, 和页面大小 按照指定字段和排序规则排列
        /// 从文章信息表中选取指定条数文章信息
        /// 如果指定int类型参数传入-1, string传入null, 则表示忽略该检索条件
        /// 将默认按照更新时间降序排序, 返回文章信息对象集合, 不存在则返回NULL(简化版)
        /// </summary>
        /// <param name="status">文章当前状态(0:公开, 1:加密, 2:私密, 3:继承, 4:草稿, 5:自动草稿, 6:回收站...)(忽略该条件则传入-1)</param>
        /// <param name="type">文章类型(0:Post, 1:Page, 2:Media...)(忽略该条件则传入-1)</param>
        /// <param name="pageSize">文章数目</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="order">排序方式(0:降序(desc)|1:升序(asc))</param>
        /// <returns>符合条件文章信息对象集合, 如果不存在则返回null</returns>
        public List<Post> SelectPostList(int status, int type, int pageSize, int pageIndex, string orderField, int order)
        {
            List<string> conditions = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            if (status != -1)
            {
                conditions.Add(" [post_status]=@Status ");
                ps.Add(new SqlParameter("@Status", status));
            }
            if (type != -1)
            {
                conditions.Add(" [post_type]=@Type ");
                ps.Add(new SqlParameter("@Type", type));
            }
            string where = ps.Count > 0 ? string.Join("AND", conditions.ToArray()) : null;
            string col = @"[post_id],
                                    [post_slug],
                                    [post_title],
                                    [post_subtitle],
                                    [post_pubdate],
                                    [post_update],
                                    [post_content],
                                    [post_excerpt],
                                    [post_template],
                                    [post_auther_id],
                                    [post_view_count],
                                    [post_comment_count],
                                    [post_comment_status],
                                    [post_status],
                                    [post_type],
                                    [post_istop],
                                    [post_ping_status],
                                    [post_pinged],
                                    [post_password],
                                    [post_parent_id]";
            string sql = SqlHelper.MakePagedSql("&#WednTablePrefix%&_Posts", col, pageSize, pageIndex, orderField, order, where);
            return Select(sql, ps.ToArray());
        }

        /// <summary>
        /// 根据指定关键词, 状态, 浏览等级, 类别ID, 用户ID, 开始时间, 结束时间, 指定页码, 和页面大小 按照指定字段和排序规则排列
        /// 从文章信息表中选取指定条数文章信息
        /// 如果指定int类型参数传入-1, string传入null, 则表示忽略该检索条件
        /// 将默认按照更新时间降序排序, 返回文章信息对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="keyword">关键词(忽略该条件则传入null)</param>
        /// <param name="status">文章当前状态(0:发布, 1:加密, 2:私密, 4:草稿, 5:回收站)(忽略该条件则传入-1)</param>
        /// <param name="type">文章类型(0:Post, 1:Page, 2:Media...)(忽略该条件则传入-1)</param>
        /// <param name="categoryId">类别ID(忽略该条件则传入-1)</param>
        /// <param name="userId">用户ID(忽略该条件则传入-1)</param>
        /// <param name="beginDate">起始日期(忽略该条件则传入null)</param>
        /// <param name="endDate">截至日期(忽略该条件则传入null)</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="order">排序方式(0:降序(desc)|1:升序(asc))</param>
        /// <param name="recordCount">返回全部记录数</param>
        /// <returns>多条文章信息集合, 如果不存在则返回null</returns>
        public List<Post> SelectPostList(string keyword, int status, int type, int categoryId, int userId, string beginDate, string endDate, int pageSize, int pageIndex, string orderField, int order, out int recordCount)
        {
            List<string> conditions = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyword))
            {
                conditions.Add(string.Format(" [post_title] LIKE '%{0}%' OR [post_subtitle] LIKE '%{0}%' OR [post_content] LIKE '%{0}%' ", keyword));
            }
            if (status != -1)
            {
                conditions.Add(" [post_status]=@Status ");
                ps.Add(new SqlParameter("@Status", status));
            }
            if (type != -1)
            {
                conditions.Add(" [post_type]=@Type ");
                ps.Add(new SqlParameter("@Type", type));
            }
            if (categoryId != -1)
            {
                int[] ids = new RelationDAO().SelectObjectId("post", categoryId);
                string str = string.Join(",", ids);
                conditions.Add(" [post_id] IN (" + str + ") ");
            }
            if (userId != -1)
            {
                conditions.Add(" [post_auther_id]=@AutherId ");
                ps.Add(new SqlParameter("@AutherId", userId));
            }
            if (!string.IsNullOrEmpty(beginDate))
            {
                conditions.Add(" [post_pubdate]>=@BeginDate ");
                ps.Add(new SqlParameter("@BeginDate", beginDate));
            }
            if (!string.IsNullOrEmpty(endDate))
            {
                conditions.Add(" [post_pubdate]<@EndDate ");
                ps.Add(new SqlParameter("@EndDate", endDate));
            }
            string where = conditions.Count > 0 ? string.Join(" AND ", conditions.ToArray()) : null;
            recordCount = SelectPostsCount(where, ps.ToArray());
            //recordCount = 0;
            //SqlParameter tc = new SqlParameter(@"totalCount", System.Data.SqlDbType.Int);
            //tc.Direction = ParameterDirection.Output;
            //tc.Value = recordCount;
            //ps.Add(tc);
            string col = @"[post_id],
                                    [post_slug],
                                    [post_title],
                                    [post_subtitle],
                                    [post_pubdate],
                                    [post_update],
                                    [post_content],
                                    [post_excerpt],
                                    [post_template],
                                    [post_auther_id],
                                    [post_view_count],
                                    [post_comment_count],
                                    [post_comment_status],
                                    [post_status],
                                    [post_type],
                                    [post_istop],
                                    [post_ping_status],
                                    [post_pinged],
                                    [post_password],
                                    [post_parent_id]";
            string sql = SqlHelper.MakePagedSql("&#WednTablePrefix%&_Posts", col, pageSize, pageIndex, orderField, order, where);
            //+";select @totalCount = count(1) from [&#WednTablePrefix%&_Posts]";
            return Select(sql, ps.ToArray());
        }

        /// <summary>
        /// 根据文章归档年月选取文章信息集合默认按照更新时间降序排序, 返回对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="beginDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="status">文章当前状态(0:发布, 1:加密, 2:私密, 3:草稿, 4:回收站)(忽略该条件则传入-1)</param>
        /// <returns>多条文章信息集合, 如果不存在则返回null</returns>
        public List<Post> SelectPostList(string beginDate, string endDate, int status)
        {
            string sql = @"SELECT 
                                        [post_id],
                                        [post_slug],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_excerpt],
                                        [post_template],
                                        [post_auther_id],
                                        [post_view_count],
                                        [post_comment_count],
                                        [post_comment_status],
                                        [post_status],
                                        [post_type],
                                        [post_istop],
                                        [post_ping_status],
                                        [post_pinged],
                                        [post_password],
                                        [post_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Posts]
                                    WHERE
                                        [post_pubdate]>=@BeginDate
                                    AND
                                        [post_pubdate]<@EndDate
                                    {0}
                                    ORDER BY
                                        [post_pubdate] Desc";
            if (status != -1)
            {
                sql = string.Format(sql, " AND [post_status]=@Status");
            }
            else
            {
                sql = string.Format(sql, string.Empty);
            }
            return Select(sql,
                new SqlParameter("@BeginDate", beginDate),
                new SqlParameter("@EndDate", endDate),
                new SqlParameter("@Status", status));
        }

        /// <summary>
        /// 根据用户ID选取文章信息集合默认按照更新时间降序排序, 返回对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <returns>多条文章信息集合, 如果不存在则返回null</returns>
        public List<Post> SelectPostList(int userid)
        {
            string sql = @"SELECT 
                                        [post_id],
                                        [post_slug],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_excerpt],
                                        [post_template],
                                        [post_auther_id],
                                        [post_view_count],
                                        [post_comment_count],
                                        [post_comment_status],
                                        [post_status],
                                        [post_type],
                                        [post_istop],
                                        [post_ping_status],
                                        [post_pinged],
                                        [post_password],
                                        [post_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Posts]
                                    WHERE
                                        [post_auther_id]=@AutherId
                                    ORDER BY
                                        [post_pubdate] Desc";
            return Select(sql,
                new SqlParameter("@AutherId", userid));
        }

        /// <summary>
        /// 根据ID数组批量获取文章信息集合, 返回对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="ids">文章ID数组</param>
        /// <param name="status">更新的状态(0:公开, 1:加密, 2:私密, 3:继承, 4:草稿, 5:自动草稿, 6:回收站...)(忽略该条件则传入-1)</param>
        /// <returns>多条文章信息集合, 如果不存在则返回null</returns>
        public List<Post> SelectPostList(int[] ids, int status)
        {
            //拼接要选取的ID
            string ps = string.Join(",", ids);
            string sql = @"SELECT 
                                        [post_id],
                                        [post_slug],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_excerpt],
                                        [post_template],
                                        [post_auther_id],
                                        [post_view_count],
                                        [post_comment_count],
                                        [post_comment_status],
                                        [post_status],
                                        [post_type],
                                        [post_istop],
                                        [post_ping_status],
                                        [post_pinged],
                                        [post_password],
                                        [post_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Posts]
                                    WHERE
                                        [post_id] in ({0})
                                    {1}
                                    ORDER BY
                                        [post_pubdate] Desc";
            if (status != -1)
            {
                sql = string.Format(sql, ps, " AND [post_status]=@Status");
            }
            else
            {
                sql = string.Format(sql, ps, string.Empty);
            }
            return Select(sql,
                new SqlParameter("@Status", status));
        }

        //        /// <summary>
        //        /// 选取文章上一篇和下一篇文章对象, 返回对象集合, 如果不存在则返回null
        //        /// </summary>
        //        /// <returns>长度为2的文章对象集合,0为上一篇, 1为下一篇 不存在则返回NULL</returns>
        //        public Post[] SelectPreviousAndNext(Post current)
        //        {
        //            Post[] pn = new Post[2];
        //            List<Post> temp = new List<Post>();
        //            //string order = "post_id";
        //            string sql = @"select top 2
        //                                        [post_id],
        //                                        [post_slug],
        //                                        [post_title],
        //                                        [post_comment_count],
        //                                        [post_view_count]
        //                                    from &#WednTablePrefix%&_Posts
        //                                    where post_id in
        //                                    (
        //                                        select min(post_id) from &#WednTablePrefix%&_Posts where post_id>@Id and post_status=0 and post_type='Post'
        //                                        union
        //                                        select max(post_id) from &#WednTablePrefix%&_Posts where post_id<@Id and post_status=0 and post_type='Post'
        //                                    )";
        //            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", current.Id)))
        //            {
        //                if (reader.HasRows)
        //                {
        //                    while (reader.Read())
        //                    {
        //                        Post post = new Post()
        //                        {
        //                            Id = reader.GetInt32(0),
        //                            Slug = reader.GetString(1),
        //                            Title = reader.IsDBNull(2) ? null : reader.GetString(2),
        //                            CommentCount = reader.GetInt32(3),
        //                            ViewCount = reader.GetInt32(4),
        //                        };
        //                        temp.Add(post);
        //                    }
        //                }
        //            }
        //            if (temp.Count == 2)
        //            {
        //                return temp.ToArray();
        //            }
        //            else if (temp[0].Id > current.Id)
        //            {
        //                pn[1] = temp[0];
        //                return pn;
        //            }
        //            else
        //            {
        //                pn[0] = temp[0];
        //                return pn;
        //            }
        //        }

        /// <summary>
        /// 选取文章归档信息集合, 返回对象集合, 如果不存在则返回null
        /// </summary>
        /// <returns>文章归档信息集合, 如果不存在则返回null</returns>
        public List<Archive> SelectArchiveList()
        {
            string cmdText = @"SELECT
	                                            CONVERT(varchar(7),[post_pubdate],23) as [date] ,
	                                            COUNT([post_id]) as [count]
                                            FROM
	                                            [&#WednTablePrefix%&_Posts]
                                            WHERE
	                                            [post_status]=0
                                            GROUP BY
	                                            (CONVERT(varchar(7),[post_pubdate],23))
                                            ORDER BY
	                                            (CONVERT(varchar(7),[post_pubdate],23))
	                                            DESC;";
            List<Archive> list = null;
            using (SqlDataReader read = SqlHelper.ExecuteReader(cmdText))
            {
                if (read.HasRows)
                {
                    list = new List<Archive>();
                    while (read.Read())
                    {
                        Archive archive = new Archive();
                        //archive.Date = Convert.ToDateTime(read["date"].ToString());
                        //archive.Count = Convert.ToInt32(read["count"]);
                        archive.Date = System.Convert.ToDateTime(read[0]);
                        archive.Count = read.GetInt32(1);
                        list.Add(archive);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 根据文章ID选取文章信息对象, 返回文章信息对象, 如果不存在则返回null
        /// </summary>
        /// <param name="id">文章ID</param>
        /// <returns>文章对象, 不存在则返回NULL</returns>
        public Post SelectPost(int id)
        {
            string sql = @"SELECT TOP 1
                                        [post_id],
                                        [post_slug],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_excerpt],
                                        [post_template],
                                        [post_auther_id],
                                        [post_view_count],
                                        [post_comment_count],
                                        [post_comment_status],
                                        [post_status],
                                        [post_type],
                                        [post_istop],
                                        [post_ping_status],
                                        [post_pinged],
                                        [post_password],
                                        [post_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Posts]
                                    WHERE
                                        [post_id]=@Id;";
            List<Post> list = Select(sql,
                new SqlParameter("@Id", id));
            return list != null ? list[0] : null;
        }

        /// <summary>
        /// 根据文章Slug选取文章信息对象, 返回文章信息对象, 如果不存在则返回null
        /// </summary>
        /// <param name="slug">文章Sulg</param>
        /// <returns>文章对象, 不存在则返回NULL</returns>
        public Post SelectPost(string slug)
        {
            string sql = @"SELECT TOP 1
                                        [post_id],
                                        [post_slug],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_excerpt],
                                        [post_template],
                                        [post_auther_id],
                                        [post_view_count],
                                        [post_comment_count],
                                        [post_comment_status],
                                        [post_status],
                                        [post_type],
                                        [post_istop],
                                        [post_ping_status],
                                        [post_pinged],
                                        [post_password],
                                        [post_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Posts]
                                    WHERE
                                        [post_slug]=@Slug;";
            List<Post> list = Select(sql,
                new SqlParameter("@Slug", slug));
            return list != null ? list[0] : null;
        }

        /// <summary>
        /// 私有执行方法, 如果不存在则返回null
        /// </summary>
        /// <param name="post"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static int ExecuteSql(Post post, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Slug", post.Slug),
                new SqlParameter("@Title", string.IsNullOrEmpty(post.Title) ? System.DBNull.Value : (object)post.Title),
                new SqlParameter("@SubTitle", string.IsNullOrEmpty(post.SubTitle) ? System.DBNull.Value : (object)post.SubTitle),
                new SqlParameter("@PubDate", post.PubDate),
                new SqlParameter("@Update", post.Update),
                new SqlParameter("@Content", post.Content),
                new SqlParameter("@Excerpt", post.Excerpt),
                new SqlParameter("@Template", post.Template),
                new SqlParameter("@AutherId", post.AutherId),
                new SqlParameter("@ViewCount", post.ViewCount),
                new SqlParameter("@CommentCount", post.CommentCount),
                new SqlParameter("@CommentStatus", post.CommentStatus),
                new SqlParameter("@Status", post.Status),
                new SqlParameter("@Type", post.Type),
                new SqlParameter("@IsTop", post.IsTop),
                new SqlParameter("@PingStatus", post.PingStatus),
                new SqlParameter("@Pinged", string.IsNullOrEmpty(post.Pinged) ? System.DBNull.Value : (object)post.Pinged),
                new SqlParameter("@Password", string.IsNullOrEmpty(post.Password) ? System.DBNull.Value : (object)post.Password),
                new SqlParameter("@ParentId", post.ParentId),
                new SqlParameter("@Id", post.Id));
        }

        /// <summary>
        ///  私有选取方法, 如果不存在则返回null
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private List<Post> Select(string sql, params SqlParameter[] parameters)
        {
            List<Post> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameters))
            {
                if (reader.HasRows)
                {
                    list = new List<Post>();
                    while (reader.Read())
                    {
                        Post post = new Post()
                        {
                            Id = reader.GetInt32(0),
                            Slug = reader.GetString(1),
                            Title = reader.IsDBNull(2) ? null : reader.GetString(2),
                            SubTitle = reader.IsDBNull(3) ? null : reader.GetString(3),
                            PubDate = reader.GetDateTime(4),
                            Update = reader.GetDateTime(5),
                            Content = reader.GetString(6),
                            Excerpt = reader.GetString(7),
                            Template = reader.GetString(8),
                            AutherId = reader.GetInt32(9),
                            ViewCount = reader.GetInt32(10),
                            CommentCount = reader.GetInt32(11),
                            CommentStatus = reader.GetInt32(12),
                            Status = reader.GetInt32(13),
                            Type = reader.GetInt32(14),
                            IsTop = reader.GetBoolean(15),
                            PingStatus = reader.GetInt32(16),
                            Pinged = reader.IsDBNull(17) ? null : reader.GetString(17),
                            Password = reader.IsDBNull(18) ? null : reader.GetString(18),
                            ParentId = reader.GetInt32(19)
                        };
                        #region 避免字段名索引
                        //Convert.ToInt32(reader["post_id"]),
                        //Convert.ToString(reader["post_slug"]),
                        //reader["post_title"] != null ? Convert.ToString(reader["post_title"]) : string.Empty,
                        //reader["post_subtitle"] != null ? Convert.ToString(reader["post_subtitle"]) : string.Empty,
                        //Convert.ToDateTime(reader["post_pubdate"]),
                        ////reader["post_update"].ToString() != string.Empty ? Convert.ToDateTime(reader["post_update"]) : new DateTime(),
                        //Convert.ToDateTime(reader["post_update"]),
                        //Convert.ToString(reader["post_content"]),
                        //Convert.ToInt32(reader["post_comment_count"]),
                        //Convert.ToInt32(reader["post_view_count"]),
                        //Convert.ToInt32(reader["post_auther_id"]),
                        //Convert.ToInt32(reader["post_status"]),
                        //Convert.ToString(reader["post_password"]),
                        //reader["post_password"] != null ? Convert.ToString(reader["post_password"]) : string.Empty,
                        //Convert.ToInt32(reader["post_parent_id"]) 
                        #endregion
                        list.Add(post);
                    }
                }
            }
            return list;
        }
    }
}
